{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from pandas import Series, DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Age</th>\n",
       "      <th>Height</th>\n",
       "      <th>Team</th>\n",
       "      <th>City</th>\n",
       "      <th>Medal</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Year</th>\n",
       "      <th>Season</th>\n",
       "      <th>Sport</th>\n",
       "      <th>Event</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">1896</th>\n",
       "      <th rowspan=\"5\" valign=\"top\">Summer</th>\n",
       "      <th rowspan=\"5\" valign=\"top\">Athletics</th>\n",
       "      <th>Athletics Men's 1,500 metres</th>\n",
       "      <td>24.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>United States</td>\n",
       "      <td>Athina</td>\n",
       "      <td>Silver</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Athletics Men's 1,500 metres</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Greece</td>\n",
       "      <td>Athina</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Athletics Men's 1,500 metres</th>\n",
       "      <td>22.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Australia</td>\n",
       "      <td>Athina</td>\n",
       "      <td>Gold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Athletics Men's 1,500 metres</th>\n",
       "      <td>23.0</td>\n",
       "      <td>154.0</td>\n",
       "      <td>Germany</td>\n",
       "      <td>Athina</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Athletics Men's 1,500 metres</th>\n",
       "      <td>21.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Greece</td>\n",
       "      <td>Athina</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                     Age  Height  \\\n",
       "Year Season Sport     Event                                        \n",
       "1896 Summer Athletics Athletics Men's 1,500 metres  24.0     NaN   \n",
       "                      Athletics Men's 1,500 metres   NaN     NaN   \n",
       "                      Athletics Men's 1,500 metres  22.0     NaN   \n",
       "                      Athletics Men's 1,500 metres  23.0   154.0   \n",
       "                      Athletics Men's 1,500 metres  21.0     NaN   \n",
       "\n",
       "                                                             Team    City  \\\n",
       "Year Season Sport     Event                                                 \n",
       "1896 Summer Athletics Athletics Men's 1,500 metres  United States  Athina   \n",
       "                      Athletics Men's 1,500 metres         Greece  Athina   \n",
       "                      Athletics Men's 1,500 metres      Australia  Athina   \n",
       "                      Athletics Men's 1,500 metres        Germany  Athina   \n",
       "                      Athletics Men's 1,500 metres         Greece  Athina   \n",
       "\n",
       "                                                     Medal  \n",
       "Year Season Sport     Event                                 \n",
       "1896 Summer Athletics Athletics Men's 1,500 metres  Silver  \n",
       "                      Athletics Men's 1,500 metres     NaN  \n",
       "                      Athletics Men's 1,500 metres    Gold  \n",
       "                      Athletics Men's 1,500 metres     NaN  \n",
       "                      Athletics Men's 1,500 metres     NaN  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filename = '../data/olympic_athlete_events.csv'\n",
    "\n",
    "df = pd.read_csv(filename,\n",
    "                index_col=['Year', 'Season', 'Sport', 'Event'],\n",
    "                usecols=['Age', 'Height', 'Team', 'Year', 'Season', 'City', 'Sport', 'Event', 'Medal'])\n",
    "df = df.sort_index()\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "25.026883940421765"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# What is the average age for winning athletes in summer games held between 1936 and 2000?\n",
    "df.loc[(slice(1936,2000), 'Summer'), 'Age'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Team\n",
       "South Korea           69\n",
       "Belgium               52\n",
       "France                48\n",
       "United States         41\n",
       "China                 19\n",
       "Italy                 15\n",
       "Great Britain         13\n",
       "Chinese Taipei         9\n",
       "Netherlands            9\n",
       "Cincinnati Archers     8\n",
       "Ukraine                8\n",
       "Soviet Union           7\n",
       "Japan                  7\n",
       "Germany                7\n",
       "Finland                6\n",
       "Australia              5\n",
       "Unified Team           4\n",
       "Russia                 4\n",
       "Boston Archers         4\n",
       "Potomac Archers        4\n",
       "Poland                 4\n",
       "Spain                  3\n",
       "Indonesia              3\n",
       "Sweden                 2\n",
       "Mexico                 2\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# What team has won the greatest number of medals for all archery events?\n",
    "(\n",
    "    df\n",
    "    .dropna(subset='Medal')\n",
    "    .loc[(slice(None), 'Summer', 'Archery'), 'Team']\n",
    "    .value_counts()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "165.04827586206898"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Starting in 1980, what is the average height of the event known as \"Table Tennis Women's Team\"?\n",
    "df.loc[(slice(1980, None), 'Summer', slice(None), \"Table Tennis Women's Team\"), 'Height'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "171.26643598615917"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Starting in 1980, what is the average height of either \"Table Tennis Women's Team\" or \"Table Tennis Men's Team\"?\n",
    "df.loc[(slice(1980, None), 'Summer', slice(None), [\"Table Tennis Men's Team\", \"Table Tennis Women's Team\"]), 'Height'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "208.0"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# How tall was the tallest-ever tennis player in Olympic games from 1980 until 2016?\n",
    "df.loc[(slice(1980,2016), 'Summer', 'Tennis'), 'Height'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
